package Server;

import java.sql.Date;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import client.Item;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

public class Order {
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters 
	 * @return ArrayList with all the Items in the Item table which were selected by users query parameters
	 */
	public static ArrayList SearchItemOredr(Connection con1,String[] log )
	{		
		ArrayList a = new ArrayList<String[]>();		
		
		String query = null;		
		try {
			java.sql.Statement stmt = con1.createStatement();						
			
			if ( (!(log[1].equals(""))) && (!(log[4].equals(""))) && (!(log[2].equalsIgnoreCase(""))) && (!(log[3].equalsIgnoreCase(""))))
			{
				int pricefrom = Integer.parseInt(log[2].trim());		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price>=" +pricefrom+ ") and (price<=" +priceuntil+ ") and (domination_color='" +log[4]+ "');";
			}
			if ( (!(log[1].equals(""))) && (log[4].equals("")) && (!(log[2].equalsIgnoreCase(""))) && (!(log[3].equalsIgnoreCase(""))))			
			{
				int pricefrom = Integer.parseInt(log[2].trim());		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price>=" +pricefrom+ ") and (price<=" +priceuntil+ ");";
			}
																								
			
			if ( (log[1].equals("")) && (!(log[4].equals(""))) && (!(log[2].equalsIgnoreCase(""))) && (!(log[3].equalsIgnoreCase(""))))
			{
				int pricefrom = Integer.parseInt(log[2].trim());		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (price>=" +pricefrom+ ") and (price<=" +priceuntil+ ") and (domination_color='" +log[4]+ "');";		
			}
			if ( (!(log[1].equals(""))) && (!(log[4].equals(""))) && (log[2].equalsIgnoreCase("")) && (!(log[3].equalsIgnoreCase(""))))
			{		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price<=" +priceuntil+ ") and (domination_color='" +log[4]+ "');";
			}
							
			if ( ((log[1].equals(""))) && ((log[4].equals(""))) && (log[2].equalsIgnoreCase("")) && (!(log[3].equalsIgnoreCase(""))))
			{		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (price<=" +priceuntil+ ");";
			}
			
			if ( (!(log[1].equals(""))) && ((log[4].equals(""))) && (log[2].equalsIgnoreCase("")) && (!(log[3].equalsIgnoreCase(""))))
			{		
				int priceuntil = Integer.parseInt(log[3].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price<=" +priceuntil+ ");";
			}
			
			if ( (!(log[1].equals(""))) && (!(log[4].equals(""))) && (!(log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				int pricefrom = Integer.parseInt(log[2].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price>=" +pricefrom+ ") and (domination_color='" +log[4]+ "');";
			}
			
			if ( ((log[1].equals(""))) && ((log[4].equals(""))) && (!(log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				int pricefrom = Integer.parseInt(log[2].trim());
				query="SELECT * FROM items WHERE (price>=" +pricefrom+ ");";
			}
			
			if ( (!(log[1].equals(""))) && ((log[4].equals(""))) && (!(log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				int pricefrom = Integer.parseInt(log[2].trim());
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (price>=" +pricefrom+ ");";
			}
			
			if ( (!(log[1].equals(""))) && (!(log[4].equals(""))) && ((log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				query="SELECT * FROM items WHERE (category='" +log[1]+"') and (domination_color='" +log[4]+ "');";
			}		
			if ( (!(log[1].equals(""))) && ((log[4].equals(""))) && ((log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				query="SELECT * FROM items WHERE (category='" +log[1]+"');";
			}
			if ( ((log[1].equals(""))) && (!(log[4].equals(""))) && ((log[2].equalsIgnoreCase(""))) && (log[3].equalsIgnoreCase("")))
			{
				query="SELECT * FROM items WHERE (domination_color='" +log[4]+ "');";
			}
			
			java.sql.ResultSet res = stmt.executeQuery(query);
			
			while (res.next()) {
				String[] temp = new String[5];
					temp[0]=Integer.toString(res.getInt(1));	//id	
					temp[1]=res.getString(3);	//name
					temp[2]=res.getString(2);	//category
					temp[3]=res.getString(4);	//descreption
					temp[4]=Integer.toString(res.getInt(5));	//price
				// create iems's instance and insert to list
				a.add(temp);
			}
			res.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return a;	
	}
	
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters 
	 * @return Vector with all the Clients subscription information: his ID and his account balance
	 */
	public static Vector checksub(Connection con1,String[] log )
	{		
				
		Vector<Object> SubDeatails = new Vector<Object>();					
		try {
			java.sql.Statement stmt = con1.createStatement();
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM clients WHERE User_name='" + log[1] + "'");
			if (res.next()) {				
				if (res.getBoolean(6) == true)
				{
					SubDeatails.addElement(new Integer(1));
					SubDeatails.addElement(new Integer(res.getInt(1)));
					SubDeatails.addElement(new Integer(res.getInt(9)));	
				}
				else
					SubDeatails.addElement(new Integer(0));	
			}
			res.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (SubDeatails);	
	}
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters-new state in the balance account 
	 * @return 
	 */
	public static void updatebalance(Connection con1,String[] log )
	{		
				
						
		try {
			java.sql.Statement stmt = con1.createStatement();
			java.sql.PreparedStatement ps = con1.prepareStatement("UPDATE clients SET money=? WHERE User_name=?");
            ps.setObject(1, log[1]);
            ps.setObject(2, log[2]); 
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * @author Daniel
* 	@param con1 Connection to the database
	 * @param log Array of String which describes users query parameters-new credit card information  
	 * @return 
	 */
	public static void enternewcreditcard(Connection con1,String[] log )
	{		
						
		try {
			java.sql.Statement stmt = con1.createStatement();
			java.sql.PreparedStatement ps1 = con1.prepareStatement("UPDATE clients SET credit_card=? WHERE User_name=?");
            ps1.setObject(1, log[2]);            
            ps1.setObject(2, log[1]);
			ps1.executeUpdate();
			java.sql.PreparedStatement ps2 = con1.prepareStatement("UPDATE clients SET card_validation=? WHERE User_name=?");
			ps2.setObject(1, log[3]);           
            ps2.setObject(2, log[1]);
			ps2.executeUpdate();
			
			
			

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters 
	 * @return Vector with all the Clients crdit-card information: his ID, his card number and expiration date
	 */
	public static Vector creditcard(Connection con1,String[] log )
	{		
				
		Vector<Object> CardDeatails = new Vector<Object>();					
		try {
			java.sql.Statement stmt = con1.createStatement();
			// execute SQL query to get all the book that match to the input
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM clients WHERE User_name='" + log[1] + "'");
			if (res.next()) {				
				if (!(res.getInt(7) == 0))
				{
					CardDeatails.addElement(new Integer(1));
					CardDeatails.addElement(new Integer(res.getInt(7)));
					CardDeatails.addElement(new Integer(res.getInt(8)));	
				}
				else
					CardDeatails.addElement(new Integer(0));	
			}
	
			res.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (CardDeatails);	
	}
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @return integer which describes how many items are in the Item table
	 */
	public static int ReturnNumOfItems(Connection con1 )
	{		
				
			int i=0;			
		try {
			java.sql.Statement stmt = con1.createStatement();
			// execute SQL query to get all the book that match to the input
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM items");
			while (res.next()) 
				i++;
							

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
		
	}
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters = first cell: Items ID. second cell: the amount reqiered.
	 * @return Array of String. in the first cell there is a code if the Item in the Inventory. in the second cell, the items name. 
	 */
	public static String[] Inventory(Connection con1,String[] log )
	{		
							
			String[] answer = new String[2];
			answer[0]="0";
			int amount=Integer.parseInt(log[2].trim());
		try {
			java.sql.Statement stmt = con1.createStatement();
			// execute SQL query to get all the book that match to the input
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM items WHERE item_num='" + log[1] + "'");
			if (res.next()) 				
			{
				answer[1]=res.getString(3);
				if (res.getInt(7)<amount)
					answer[0]="1";	
			}
							

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return answer;
		
	}
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Vector of all the information about the order: the UserName of the client who made the order. the itmes and their quantity, the price of the order, greeting card, delivary information and the type of payment. 
	 */
	public static void MainOrder(Connection con1,Vector<Object> log )
	{		
		int RowNum=0;
		int OrderNum=0;
		String user = (String) log.get(1);
		Vector<Integer> items=(Vector<java.lang.Integer>) log.get(2);
		String[] greeting = (java.lang.String[]) log.get(3);
		String[] sum = (java.lang.String[]) log.get(4);
		String[] delivary = (java.lang.String[]) log.get(5);
		String[] pay = (java.lang.String[]) log.get(6);
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Calendar cal = Calendar.getInstance();
		   //System.out.println(dateFormat.format(cal.getTime()));
		
		try {
			java.sql.Statement stmt = con1.createStatement();
			// execute SQL query to get all the book that match to the input
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM orders");
			while (res.next()) 
				RowNum++;
							

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			java.sql.Statement stmt = con1.createStatement();
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM orders WHERE row_num="+RowNum);
			if (res.next()) 				
			{
				OrderNum=res.getInt(2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		RowNum++;
		OrderNum++;
		int NumOfItems=items.size()/2;
		int j=0;
		for (int i=0;i<NumOfItems;i++)
		{
			
			
			// execute SQL query to get all the book that match to the input
			try {
				java.sql.Statement stmt = con1.createStatement();
				java.sql.PreparedStatement ps = con1.prepareStatement("INSERT INTO orders VALUES("+RowNum+", "+OrderNum+", '"+user+"', "+items.get(j)+", "+items.get(j+1)+", '"+greeting[1]+"', "+sum[1]+", '"+delivary[4]+"', '"+delivary[5]+"', '"+delivary[1]+"', '"+delivary[2]+"', '"+delivary[3]+"', '"+pay[0]+"', '"+dateFormat.format(cal.getTime())+"', 1)");

	            ps.executeUpdate();    
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			RowNum++;
			j=j+2;	
		}
		j=0;
		
		for (int i=0;i<NumOfItems;i++)
		{
			
		try {
			int OldAmount=0;
			java.sql.Statement stmt = con1.createStatement();
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM items WHERE item_num="+items.get(j));
			if (res.next()) 				
			{
				OldAmount=res.getInt(7);	
			}
					int NewAmount=OldAmount-items.get(j+1);	
			// execute SQL query to get all the book that match to the input
			java.sql.PreparedStatement ps = con1.prepareStatement("UPDATE items SET quantity="+NewAmount+" WHERE item_num="+items.get(j)+"");
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		j=j+2;
		}
		
		}	
	/**
	 * @author Daniel
	 * @param con1 Connection to the database
	 * @param log Array of String which describes users query parameters
	 * @return picture of the Item
	 */
	public static Item GetPic(Connection con1,String[] log )
	{		
			Item it=null;						   		
		try {
			java.sql.Statement stmt = con1.createStatement();
			java.sql.ResultSet res = stmt.executeQuery("SELECT * FROM items WHERE item_num="+log[1]+"");
			if (res.next()) {
				it = new Item(res.getString(8));
					//1 - item_num, 2 - name, 3 - category, 4- description, 5 - price,
					//6 - quantity, 7 - special sale, 8 - picture, 9 - inCatalog, 10 - inSale 				
			}
			} catch (SQLException e) {
				e.printStackTrace();
				}           
		return (it);// return list with all the answers
		
	}
	
	
}


